package com.api;

import com.mapper.sql.poiMapper_sql;
import com.mapper.sqlite.poiMapper_sqlite;
import com.model.Student;
import com.model.Teacher;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class POIWrite {
    //以形参的方式传入sql、sqlite调用
    public static String poiListExcel(poiMapper_sql sqlMapper, poiMapper_sqlite sqliteMapper)throws Exception{

       //创建格式化时间内容(当数据库表有时间的情况)
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdf1=new SimpleDateFormat("yyy-MM-dd hh:mm:ss");

        //创建一个workbook 对应一个Excel文件
        HSSFWorkbook workbook=new HSSFWorkbook();

        //创建excel的标题行,将student和teacher信息发、分别放置在不同的sheet上
        //学生字段有name,age,score,time
        //老师字段有name,age,type
        String[] title_Student={"序号","学生姓名","学生年龄","学生分数","发布时间"};
        String[] title_Teacher={"序号","老师姓名","老师年龄","教书类型"};

        //设置sheet名称，并创建新的sheet对象
        String sheetName_student="学生信息一览";
        Sheet stuSheet =workbook.createSheet(sheetName_student);

        String sheetName_teacher="教师信息一览";
        Sheet teaSheet =workbook.createSheet(sheetName_teacher);

        //获取表头行
        //poi以row、cell表示横纵
        Row titleRow=stuSheet.createRow(0);
        Row titleRow_tea=teaSheet.createRow(0);
        //创建单元格，设置style居中，字体、单元格大小
        CellStyle style = workbook.createCellStyle();
        Cell cell=null;

        //把写好的标题行写入excel
        for(int i =0;i<title_Student.length;i++){
            cell=titleRow.createCell(i);
            cell.setCellValue(title_Student[i]);
            cell.setCellStyle(style);
        }
        for(int i=0;i<title_Teacher.length;i++){
            cell=titleRow_tea.createCell(i);
            cell.setCellValue(title_Teacher[i]);
            cell.setCellStyle(style);
        }

        //创建一个List集合接收从数据库获取的student信息
        //并从形参获取的数据库获取查询
        List<Student> studentInfo=sqlMapper.findStudentInfo();
        System.out.println(studentInfo);
        //创建一个List集合接收从数据库获取的teacher信息
        List<Teacher> teacherInfo=sqliteMapper.findTeacherInfo();
        System.out.println(teacherInfo);
        //把数据库取到的数据写入excel
        Row row=null;
//        遍历student信息加入excel
        for(int i=0;i<studentInfo.size();i++){
            //创建list.size()行数据
            row=stuSheet.createRow(i+1);

            //把值一一写入单元格
            //设置第一列自动递增的序号
            row.createCell(0).setCellValue(i+1);
            row.createCell(1).setCellValue(studentInfo.get(i).getName());
            row.createCell(2).setCellValue(studentInfo.get(i).getAge());
            row.createCell(3).setCellValue(studentInfo.get(i).getScore());
            row.createCell(4).setCellValue(sdf1.format(studentInfo.get(i).getTime()));

            //遍历teacher信息加入excel
            for(int j=0;j<teacherInfo.size();j++){
                //创建list.size()行数据
                row=teaSheet.createRow(j+1);

                //把值一一写入单元格
                //设置第一列自动递增的序号
                row.createCell(0).setCellValue(j+1);
                row.createCell(1).setCellValue(teacherInfo.get(j).getName());
                row.createCell(2).setCellValue(teacherInfo.get(j).getAge());
                row.createCell(3).setCellValue(teacherInfo.get(j).getType());
            }
        }


        //单元格自适应
        for(int i=0;i<title_Student.length;i++){
            stuSheet.autoSizeColumn(i,true);
            stuSheet.setColumnWidth(i,stuSheet.getColumnWidth(i)*15/10);
        }
        for(int i=0;i<title_Teacher.length;i++){
            teaSheet.autoSizeColumn(i,true);
            teaSheet.setColumnWidth(i,teaSheet.getColumnWidth(i)*15/10);
        }

        //获取配置文件中保存对应excel文件的路径，本地写成如：D：xxx/xxx
        String folderPath ="D:\\教师学生信息";
        //创建上传文件目录
        File folder =new File(folderPath);

        //判断文件已经存在
        if(!folder.exists()){
            folder.mkdir();
        }
        //设置文件名。这里以文本+时间的形式创建
        String excelName="学生教师信息";
        String time=sdf.format(new Date());
        String fileName=excelName+time+".xlsx";

        //设置文件输出路径
        String savePath =folderPath+File.separator+fileName;
        OutputStream fileOut=new FileOutputStream(savePath);
        workbook.write(fileOut);
        fileOut.close();

        return savePath;
    }
}
